package lesson.ch9;

import java.sql.Connection;
import java.sql.DatabaseMetaData;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;

public class OracleDbDemo {
	private Connection conn;
	private String url;
	private String user;
	private String pass;

	static {
		try {
			Class.forName("oracle.jdbc.driver.OracleDriver");
		} catch (ClassNotFoundException e) {
			e.printStackTrace();
		}
	}

	public OracleDbDemo(String url, String user, String pass) {
		this.url = url;
		this.user = user;
		this.pass = pass;
		try {
			conn = DriverManager.getConnection(url, user, pass);
		} catch (SQLException e) {
			e.printStackTrace();
		}
	}

	private void reConnect() {
		try {
			if (conn.isClosed()) {
				conn = DriverManager.getConnection(url, user, pass);
			}
		} catch (SQLException e) {
			e.printStackTrace();
		}
	}

	private void desc(ResultSet rs) {
		int cols = 0;
		String title = "";
        reConnect();
		System.out.println("METADATA");
		System.out.println("==========================");
		try {
			cols = rs.getMetaData().getColumnCount();
			for (int i = 1; i <= cols; i++) {
				String ty = rs.getMetaData().getColumnTypeName(i);
				String name = rs.getMetaData().getColumnLabel(i);
				int size = rs.getMetaData().getColumnDisplaySize(i);
				System.out.println(name + '|' + ty + '|' + size);
				title = title + name + '|';
			}
		} catch (SQLException se) {
			// Handle errors for JDBC
			se.printStackTrace();
		} catch (Exception e) {
			// Handle errors for Class.forName
			e.printStackTrace();
		}
		System.out.println("==========================");

		System.out.println("DATA");
		System.out.println(title);
		try {
			while (rs.next()) {
				for (int i = 1; i <= cols; i++) {
					System.out.print(rs.getString(i) + "|");
				}
				System.out.println();
			}
		} catch (SQLException se) {
			// Handle errors for JDBC
			se.printStackTrace();
		} catch (Exception e) {
			// Handle errors for Class.forName
			e.printStackTrace();
		} finally {
			try {
				rs.close();
				conn.close();
			} catch (SQLException e) {
				// TODO Auto-generated catch block
				e.printStackTrace();
			}
		}
	}

	private void getAllTables() {
		DatabaseMetaData DMD = null;
		ResultSet rs = null;
		reConnect();
		try {
			DMD = conn.getMetaData();
			String[] types = { "TABLE" };
			rs = DMD.getTables(null, DMD.getUserName(), "%", types);
		} catch (SQLException se) {
			// Handle errors for JDBC
			se.printStackTrace();
		} catch (Exception e) {
			// Handle errors for Class.forName
			e.printStackTrace();
		}
		desc(rs);
	}

	public void close() {
		try {
			conn.close();
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
	}

	public void getTableData(String tab, int limitRows) {
		Statement stmt = null;
		ResultSet rs = null;
		reConnect();
		try {
			stmt = conn.createStatement();
			rs = stmt.executeQuery("select * from " + tab.toUpperCase() + " where rownum<=" + limitRows);
		} catch (SQLException se) {
			// Handle errors for JDBC
			se.printStackTrace();
		} catch (Exception e) {
			// Handle errors for Class.forName
			e.printStackTrace();
		}
		desc(rs);
	}

	public static void main(String[] args) throws SQLException {
		try {
			Class.forName("oracle.jdbc.driver.OracleDriver");
		} catch (ClassNotFoundException e) {
			e.printStackTrace();
		}
		String url = "jdbc:oracle:thin:@192.168.21.215:1523/tas_taf";
		String user = "nick";
		String pass = "nick";
		OracleDbDemo ora = new OracleDbDemo(url, user, pass);
		ora.getAllTables();
		ora.getTableData("INDEX_DB", 10);
	}
}